package model;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.*;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;

import org.apache.struts.upload.FormFile;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;

public class StudentModel {

	private static String connection = "jdbc:postgresql://localhost/cse135?user=postgres&password=password";
	
	/* Write applicants and degrees */
	
	public static int writeApplicant(String firstName, String lastName, String miName, 
		String citizenship, String residency, String address, String city, String state,
		String zipCode, String areaCode, String countryCode, String phone, String permanent,
		String specialization, String user) throws DbException {
		
		ResultSet rs = null;
		int aid = -1;
		try {
			
			Connection conn = DbConnectionPool.getConnection();
			
			Statement statement = conn.createStatement();
			
			
			int citId = StudentModel.findCountryId(citizenship, statement);
			int resId = StudentModel.findCountryId(residency, statement);
			int specId = StudentModel.findSpecializationId(specialization, statement);
			
			PreparedStatement pstmt = conn.prepareStatement("INSERT INTO applicants"
			    	+ "(first_name, middle_name, last_name, street_address, city, state, zip_code, area_code, phone_number, country_code,"
					+ "us_resident, status, citizenship, residency, specialization, user_name)"
					+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
						
			pstmt.setString(1, firstName);
		    pstmt.setString(2, miName);
		    pstmt.setString(3, lastName);
		    pstmt.setString(4, address);
		    pstmt.setString(5, city);
		    pstmt.setInt(7, Integer.parseInt(zipCode));
			
		    if (residency.equals("United States")) {
		    	pstmt.setString(6, state);
		    	pstmt.setInt(8, Integer.parseInt(areaCode));
		    	pstmt.setInt(10, -1);
		    }
		    else {
		    	pstmt.setString(6, "");
		    	pstmt.setInt(8, -1);
		    	pstmt.setInt(10, Integer.parseInt(countryCode));
		    }

		    pstmt.setInt(9, Integer.parseInt(phone));
		    pstmt.setBoolean(11, Boolean.parseBoolean(permanent));
		    
		    pstmt.setString(12, "pending");
		    
		    pstmt.setInt(13, citId);
		    pstmt.setInt(14, resId);
		    pstmt.setInt(15, specId);			
		    pstmt.setString(16, user);
		    
		    pstmt.executeUpdate();
		    
		    conn.commit();
		    
		    /* SELECT  to find id of degree reference */		    
		    rs = statement.executeQuery("SELECT * FROM applicants");
		    while (rs.next())
		    	aid = rs.getInt("id");

		    rs.close();
		    pstmt.close();
			conn.close();

		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
		return aid;
	}
	
	public static void writeDegree(HashMap degree, int appId) throws DbException {
		ResultSet rs = null;
		int aid = -1;
		try {
			String university = (String)degree.get("university");
			String discipline = (String)degree.get("discipline");
			String month = (String)degree.get("month");
			String year = (String)degree.get("year");
			String gpa = (String)degree.get("gpa");
			String title = (String)degree.get("title");
			
			Connection conn = DbConnectionPool.getConnection();
			
			Statement statement = conn.createStatement();
			
			int uniId = StudentModel.findUniversityId(university, statement);
			
			int disId;
	    	
	    	if ( discipline.equals("Computer Science") 
	    			|| discipline.equals("Computer Engineering")
	    			|| discipline.equals("Computer Science - Bionformatics")
	    			|| discipline.equals("Electrical Engineering") ) {
	    		rs = statement.executeQuery("SELECT id FROM disciplines WHERE discipline = '" + discipline + "'");
	        	rs.next();
	        	disId = rs.getInt("id");
	    	}
	    	else {
	    		disId = 5;
	    	}
	    	
	    	PreparedStatement pstmt = conn.prepareStatement("INSERT INTO degrees"
	    			+ "(grad_month, grad_year, gpa, title, transcript, completed_by, discipline, university)"
	    			+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
	    	
	    	pstmt.setInt(1, Integer.parseInt(month));
	    	pstmt.setInt(2, Integer.parseInt(year));
	    	pstmt.setString(3, gpa);
	    	pstmt.setString(4, title);
	    	pstmt.setString(5, "");
	    	pstmt.setInt(6, appId);
	    	pstmt.setInt(7, disId);
	    	pstmt.setInt(8, uniId);
	    	
	    	pstmt.executeUpdate();
	    	
	    	conn.commit();
			
		    rs.close();
		    pstmt.close();
			conn.close();

		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static int findCountryId(String country, Statement state) throws DbException {
		int cid = -1;
		ResultSet rs = null;
		
		try {
			rs = state.executeQuery("SELECT id FROM countries WHERE country = '" + country + "'");
			rs.next();
			cid = rs.getInt("id");
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
		
		return cid;
	}
	
	public static int findSpecializationId(String specialization, Statement state) throws DbException {
		int cid = -1;
		ResultSet rs = null;
		
		try {
			rs = state.executeQuery("SELECT id FROM specializations WHERE specialization = '" + specialization + "'");
			rs.next();
			cid = rs.getInt("id");
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
		
		return cid;
	}
	
	public static int findUniversityId(String university, Statement state) throws DbException {
		int uid = -1;
		ResultSet rs = null;
		
		try {
			rs = state.executeQuery("SELECT id FROM universities WHERE university = '" + university + "'");
			rs.next();
			uid = rs.getInt("id");
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
		
		return uid;
	}
	
	
	/* fetching students and degrees */
	
	public static boolean userHasApplication(String user) throws DbException {
		ResultSet rs = null;
		
		int isEmpty = 0;
			
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			rs = statement.executeQuery("SELECT * FROM applicants WHERE user_name = '" + user + "'");
			
			while (rs.next()) {
				isEmpty++;
			}
			
			statement.close();
			conn.close();
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
		
		if (isEmpty > 0)
			return true;
		
		return false;
	}
	
	public static HashMap getApplicationByUser(String user) throws DbException {
		ResultSet rs = null;
		
		HashMap application = new HashMap();
			
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			rs = statement.executeQuery("SELECT * FROM applicants WHERE user_name = '" + user + "'");
			
			rs.next();
			
			// put into the map the regular value
			application.put("firstName", rs.getString("first_name"));
			application.put("lastName", rs.getString("last_name"));
			application.put("miName", rs.getString("middle_name"));
			application.put("address", rs.getString("street_address"));
			application.put("city", rs.getString("city"));
			application.put("state", rs.getString("state"));
			application.put("zipCode", rs.getInt("zip_code"));
			application.put("areaCode", rs.getInt("area_code"));
			application.put("phone", rs.getInt("phone_number"));
			application.put("countryCode", rs.getInt("country_code"));
			application.put("permanent", rs.getBoolean("us_resident"));
			application.put("status", rs.getString("status"));
			
			// go to the database for the references
			int appId = rs.getInt("id");
			
			application.put("degrees", StudentModel.getDegreesByAppId(appId));
			
			int citId = rs.getInt("citizenship");		
			int resId = rs.getInt("residency");
			int specId = rs.getInt("specialization");
			
			rs = statement.executeQuery("SELECT country FROM countries WHERE id = '" + citId + "'");
			rs.next();
			application.put("citizenship", rs.getString("country"));
			
			rs = statement.executeQuery("SELECT country FROM countries WHERE id = '" + resId + "'");
			rs.next();
			application.put("residency", rs.getString("country"));
			
			rs = statement.executeQuery("SELECT specialization FROM specializations WHERE id = '" + specId + "'");
			rs.next();
			application.put("specialization", rs.getString("specialization"));
			
			statement.close();
			conn.close();
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
		
		
		return application;
	}
	
	public static Vector getDegreesByAppId(int appId) throws DbException {
		ResultSet rs = null;
		
		Vector degrees = new Vector();
			
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			rs = statement.executeQuery("SELECT * FROM degrees WHERE completed_by = '" + appId + "'");
						
			// put into the map the regular value
			while (rs.next()) {
				HashMap degree = new HashMap();
				
				degree.put("month", rs.getInt("grad_month"));
				degree.put("year", rs.getInt("grad_year"));
				degree.put("gpa", rs.getString("gpa"));
				degree.put("title", rs.getString("title"));
				
				// go to the database for the references
				int disId = rs.getInt("discipline");
				int uniId = rs.getInt("university");
				
				degree.put("discipline", StudentModel.getDisciplineFromId(disId, conn.createStatement()));
				degree.put("university", StudentModel.getUniversityFromId(uniId, conn.createStatement()));
								
				degrees.add(degree);
			}
			
			statement.close();
			conn.close();
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
		
		return degrees;
	}
	
	public static String getDisciplineFromId(int id, Statement state) throws DbException {
		String disc = null;
		ResultSet rs = null;
		
		try {
			rs = state.executeQuery("SELECT discipline FROM disciplines WHERE id = '" + id + "'");
			rs.next();
			disc = rs.getString("discipline");
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
		
		return disc;
	}
	
	public static String getUniversityFromId(int id, Statement state) throws DbException {
		String uni = null;
		ResultSet rs = null;
		
		try {
			rs = state.executeQuery("SELECT university FROM universities WHERE id = '" + id + "'");
			rs.next();
			uni = rs.getString("university");
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
		
		return uni;
	}
	
	/* Chair Stuff */
	public static Vector getAllReviewers() {
		ResultSet rs = null;
		
		Vector reviewers = new Vector();
			
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			rs = statement.executeQuery("SELECT u.*, ur.* FROM users u, user_roles ur WHERE ur.role = 'reviewer' AND ur.user_name = u.user_name");
			while (rs.next()) {
				HashMap user = new HashMap();
				user.put("userName", rs.getString("user_name"));
				user.put("password", rs.getString("password"));
				user.put("email", rs.getString("email"));
				reviewers.add(user);
			}
			
			statement.close();
			conn.close();

		} catch (SQLException ex) {
		} catch (NamingException ex) {
		}
		return reviewers;
	}
	
	public static int numReviewers() {
		ResultSet rs = null;
		
		int numRev = 0;
		
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
		
			rs = statement.executeQuery("SELECT u.*, ur.* FROM users u, user_roles ur WHERE ur.role = 'reviewer' AND ur.user_name = u.user_name");
			while(rs.next()) {
		        numRev++;
			}

			statement.close();
			conn.close();
			
		} catch (NamingException ex) {
		} catch (SQLException ex) {
		}
		
		return numRev;
	}
	
	public static void deleteReviewer(String reviewer) {
		try {
						
			Connection conn = DbConnectionPool.getConnection();
			PreparedStatement pstmt;
			
			pstmt = conn.prepareStatement("DELETE FROM user_roles where user_name = '" + reviewer + "' AND role = 'reviewer'");
			pstmt.executeUpdate();
			
			pstmt = conn.prepareStatement("DELETE FROM users where user_name = '" + reviewer + "'");
			pstmt.executeUpdate();
				
			// Commit transaction
			conn.commit();
		
			pstmt.close();
			conn.close();
		} catch (NamingException ex) {
		} catch (SQLException ex) {
		}
	}
	
	public static void insertReviewer(String name, String password) {
		try {
			Connection conn = DbConnectionPool.getConnection();
			PreparedStatement pstmt;
			
			pstmt = conn.prepareStatement("INSERT INTO users(user_name,password) VALUES(?, md5(?))");
			pstmt.setString(1, name);
	    	pstmt.setString(2, password);
	    	pstmt.executeUpdate();
	
	    	pstmt = conn.prepareStatement("INSERT INTO user_roles(user_name, role)" + "VALUES(?, ?)");
	    	pstmt.setString(1, name);
	    	pstmt.setString(2, "reviewer");
	    	pstmt.executeUpdate();			
		
			// Commit transaction
			conn.commit();
		
			pstmt.close();
			conn.close();
		} catch (NamingException ex) {
		} catch (SQLException ex) {
		}
	}
	
	public static Vector getAllApplicantIds() {
		ResultSet rs = null;
		
		Vector applicants = new Vector();
			
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			rs = statement.executeQuery("SELECT id FROM applicants");
			while (rs.next()) {
				applicants.add(rs.getInt("id"));
			}
			
			statement.close();
			conn.close();

		} catch (SQLException ex) {
		} catch (NamingException ex) {
		}
		return applicants;
	}
	
	public static void writeReviewerAssignments(HashMap assignments) {
		ResultSet rs = null;
		
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			PreparedStatement pstmt;
			
			Vector reviewers = new Vector(assignments.keySet());
			
			for (int i = 0; i < reviewers.size(); i++) {
				rs = statement.executeQuery("SELECT user_id FROM users WHERE user_name = '" + reviewers.get(i) + "'");
				rs.next();
				int revId = rs.getInt("user_id");
								
				pstmt = conn.prepareStatement("INSERT INTO reviewer_assignments(reviewer, application) VALUES(?, ?)");
				pstmt.setInt(1, revId);
		    	pstmt.setInt(2, (Integer)assignments.get(reviewers.get(i)));
		    	pstmt.executeUpdate();
			}
		
			// Commit transaction
			conn.commit();
			statement.close();
			conn.close();
		} catch (NamingException ex) {
		} catch (SQLException ex) {
		}
	}
	
	public static boolean reviewStarted() {
		ResultSet rs = null;
		boolean started = false;
		
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			int i = 0;
			rs = statement.executeQuery("SELECT * FROM reviewer_assignments");
			while (rs.next()) {
				i++;
			}
			
			if (i > 0)
				started = true;
		
			// Commit transaction
			conn.commit();
			statement.close();
			conn.close();
		} catch (NamingException ex) {
		} catch (SQLException ex) {
		}
		
		return started;
	}
	
	public static Vector getApplicationsByReviewer(String reviewer) {
		ResultSet rs = null;
		
		Vector apps = new Vector();
			
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			rs = statement.executeQuery("SELECT user_id FROM users WHERE user_name = '" + reviewer + "'");
			rs.next();
			int revId = rs.getInt("user_id");
			
			rs = statement.executeQuery("SELECT * FROM reviewer_assignments WHERE reviewer = '" + revId + "'");
			while (rs.next()) {
				int appId = rs.getInt("application");
				HashMap student = StudentModel.getApplicationById(appId);
				apps.add(student);
			}
			
			statement.close();
			conn.close();
			

		} catch (SQLException ex) {
		} catch (NamingException ex) {
		}
		return apps;
	}
	
	public static HashMap getApplicationById(int appId) {
		ResultSet rs = null;
		
		HashMap application = new HashMap();
			
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			rs = statement.executeQuery("SELECT * FROM applicants WHERE id = '" + appId + "'");
			
			rs.next();
			
			// put into the map the regular value
			application.put("firstName", rs.getString("first_name"));
			application.put("lastName", rs.getString("last_name"));
			application.put("miName", rs.getString("middle_name"));
			application.put("address", rs.getString("street_address"));
			application.put("city", rs.getString("city"));
			application.put("state", rs.getString("state"));
			application.put("zipCode", rs.getInt("zip_code"));
			application.put("areaCode", rs.getInt("area_code"));
			application.put("phone", rs.getInt("phone_number"));
			application.put("countryCode", rs.getInt("country_code"));
			application.put("permanent", rs.getBoolean("us_resident"));
			application.put("status", rs.getString("status"));
			
			// go to the database for the references
			int id = rs.getInt("id");
			application.put("id", id);
			
			application.put("degrees", StudentModel.getDegreesByAppId(id));
			
			int citId = rs.getInt("citizenship");		
			int resId = rs.getInt("residency");
			int specId = rs.getInt("specialization");
			
			rs = statement.executeQuery("SELECT country FROM countries WHERE id = '" + citId + "'");
			rs.next();
			application.put("citizenship", rs.getString("country"));
			
			rs = statement.executeQuery("SELECT country FROM countries WHERE id = '" + resId + "'");
			rs.next();
			application.put("residency", rs.getString("country"));
			
			rs = statement.executeQuery("SELECT specialization FROM specializations WHERE id = '" + specId + "'");
			rs.next();
			application.put("specialization", rs.getString("specialization"));
			
			String aveGrade = StudentModel.getAveGradeByAppId(appId);
			application.put("aveGrade", aveGrade);
			
			statement.close();
			conn.close();
			
		} catch (SQLException ex) {
		} catch (NamingException ex) {
		} catch (DbException e) {
		}
		
		return application;
	}
	
	public static Vector getApplications() {
		ResultSet rs = null;
		
		Vector apps = new Vector();
			
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			rs = statement.executeQuery("SELECT * FROM applicants");
			while (rs.next()) {
				int appId = rs.getInt("id");
				HashMap student = StudentModel.getApplicationById(appId);
				apps.add(student);
			}
			

			statement.close();
			conn.close();
			
		} catch (SQLException ex) {
		} catch (NamingException ex) {
		}
		return apps;
	}
	
	public static String getAveGradeByAppId(int appId) {
		ResultSet rs = null;
		
		int aveGrade = 0;
			
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			rs = statement.executeQuery("SELECT * FROM reviewer_assignments WHERE application = '" + appId + "'");
			int i = 0;
			int partSum = 0;
			while (rs.next()) {
				partSum += rs.getInt("grade");
				i++;
			}
			
			if (i == 0)
				return "No Grades";
			
			aveGrade = partSum / i;
			
			statement.close();
			conn.close();

		} catch (SQLException ex) {
		} catch (NamingException ex) {
		}
		return "" + aveGrade;
	}
	
	public static Vector getApplicationsBySpecialization(String spec) {
		ResultSet rs = null;
		
		Vector apps = new Vector();
			
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			rs = statement.executeQuery("SELECT id FROM specializations WHERE specialization = '" + spec + "'");
			rs.next();
			int specId = rs.getInt("id");
			
			rs = statement.executeQuery("SELECT * FROM applicants WHERE specialization = '" + specId + "'");
			while (rs.next()) {
				int appId = rs.getInt("id");
				HashMap student = StudentModel.getApplicationById(appId);
				apps.add(student);
			}
			
			statement.close();
			conn.close();
			
		} catch (SQLException ex) {
		} catch (NamingException ex) {
		}
		return apps;
	}
	
	public static Vector getApplicationsByDiscipline(String disc) {
		ResultSet rs = null;
		
		Vector apps = new Vector();
			
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			rs = statement.executeQuery("SELECT s.* FROM applicants s, disciplines dp, degrees deg WHERE deg.completed_by=s.id AND deg.discipline=" +
					"dp.id AND dp.discipline='" + disc + "'");
			while (rs.next()) {
				int appId = rs.getInt("id");
				HashMap student = StudentModel.getApplicationById(appId);
				apps.add(student);
			}
			
			statement.close();
			conn.close();

		} catch (SQLException ex) {
		} catch (NamingException ex) {
		}
		return apps;
	}
	
	public static Vector getAllSpecializations() {
		ResultSet rs = null;
		
		Vector specializations = new Vector();
			
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			rs = statement.executeQuery("SELECT * FROM specializations");
			while (rs.next()) {
				specializations.add(rs.getString("specialization"));
			}
			
			statement.close();
			conn.close();

		} catch (SQLException ex) {
		} catch (NamingException ex) {
		}
		return specializations;
	}
	
	public static Vector getAllDisciplines() {
		ResultSet rs = null;
		
		Vector disciplines = new Vector();
			
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			rs = statement.executeQuery("SELECT * FROM disciplines");
			while (rs.next()) {
				disciplines.add(rs.getString("discipline"));
			}
			
			statement.close();
			conn.close();

		} catch (SQLException ex) {
		} catch (NamingException ex) {
		}
		return disciplines;
	}
	
	public static void updateStatus(String status, int appId) {

		try {
			
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			PreparedStatement pstmt = conn.prepareStatement("UPDATE applicants SET status = '"+ status
				+ "' WHERE id = '" + appId+ "'");
		    
		    pstmt.executeUpdate();
		    
		    conn.commit();
		    
		    pstmt.close();
			conn.close();

		} catch (SQLException ex) {
		} catch (NamingException ex) {
		}

	}
	
	public static Vector getReviewsByAppId(int appId) {
		ResultSet rs = null;
		
		Vector revs = new Vector();
			
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			
			rs = statement.executeQuery("SELECT * FROM reviewer_assignments WHERE application = '" + appId + "'");
			while (rs.next()) {
				HashMap review = new HashMap();
				review.put("grade", rs.getInt("grade"));
				review.put("comment", rs.getString("comment"));
				revs.add(review);
			}
			
			statement.close();
			conn.close();
			

		} catch (SQLException ex) {
		} catch (NamingException ex) {
		}
		return revs;
	}
	
	
	/* starting review process stuff */
	
	
	/* Read from Countries and Universities DBs */
	public static CachedRowSet getCountries() throws DbException {

		try {
			
			Connection conn = DriverManager.getConnection(connection);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement("SELECT country FROM countries ORDER BY id");

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allCountries = pStmt.executeQuery();

			CachedRowSet crsCountries = new CachedRowSetImpl();
			crsCountries.populate(allCountries);

			allCountries.close();
			pStmt.close();
			conn.close();

			return crsCountries;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} 
	}
	
	public static CachedRowSet getUnivercities() throws DbException {

		try {
			Connection conn = DriverManager.getConnection(connection);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement("SELECT location university FROM universities ORDER BY id");

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allUnivercities = pStmt.executeQuery();

			CachedRowSet crsUnivercities = new CachedRowSetImpl();
			crsUnivercities.populate(allUnivercities);

			allUnivercities.close();
			pStmt.close();
			conn.close();

			return crsUnivercities;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} 
	}
}
